package org.example.jbdc;

import com.sun.istack.internal.Nullable;

import java.sql.*;
import java.util.Arrays;

public class JDBCDemo {

    private String url;
    private String username;
    private String password;

    public Statement statement;

    public Connection con;


    public JDBCDemo(String url, String username, String password) {

        this.url = url;
        this.username = username;
        this.password = password;

        try {
            this.connect();
        } catch (SQLException e) {
            System.out.println("连接失败");
            e.printStackTrace();
        }
    }

    // 连接数据库
    public void connect() throws SQLException {
        con = DriverManager.getConnection(url, username, password);
        statement = con.createStatement();
        System.out.println("已连接到" + url);
    }

    public void disconnect() {
        try {
            con.close();
            statement.close();
            System.out.println("关闭连接");
        } catch (SQLException e) {
            System.out.println("关闭连接失败");
            e.printStackTrace();
        }
    }

    // 使用executeUpdate()方法执行INSERT、UPDATE、DELETE等DML语句，以及DDL语句
    public int handleUpdate(String sql) {
        try {
            return statement.executeUpdate(sql);
        } catch (SQLException e) {
            System.out.println("执行" + sql + "失败");
            e.printStackTrace();
            return -1;
        }
    }

    // 使用executeQuery()方法执行SELECT(DQL)语句并获取结果集：
    public ResultSet handleQuery(String sql) {
        try {
            // 使用statement
            return statement.executeQuery(sql);
        } catch (SQLException e) {
            System.out.println("执行" + sql + "失败");
            e.printStackTrace();
            return null;
        }
    }

    // 使用prepareStatement()方法执行DML语句，以及DDL语句
    // 使用prepareStatement优点
    // 1. 避免SQL注入攻击
    // 2. 避免拼接SQL语句，防止SQL语句错误
    // 原理：预编译SQL语句，将SQL语句发送到数据库服务器，数据库服务器先编译SQL语句，然后返回编译结果给客户端，客户端再执行编译结果。
    // 同时对语句进行转义，防止sql注入
    // 3. 减少SQL语句执行次数，提高性能
    public int handleUpdate(String sql, @Nullable Object[] params) {
        if (params == null) {
            return handleUpdate(sql);
        }

        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i + 1, params[i]);
            }
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            System.out.println("执行" + sql + "失败" + "\nparams: " + Arrays.toString(params));
            return -1;
        }
    }

    public ResultSet handleQuery(String sql, @Nullable Object[] params) {
        if (params == null) {
            return handleQuery(sql);
        }

        PreparedStatement preparedStatement = null;
        try {
            // 字面意思，准备状态，传入了sql语句先用？占位，然后再设置参数
            preparedStatement = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i + 1, params[i]);
            }
            return preparedStatement.executeQuery();
        } catch (SQLException e) {
            System.out.println("执行" + sql + "失败" + "\nparams: " + Arrays.toString(params));
            return null;
        }
    }

    // 处理事务
    public void handleTransaction(String[] sqls) {
        try {
            // 首先设置事务自动提交为false
            con.setAutoCommit(false);
            // 执行批量的sql语句
            for (String sql : sqls) {
                statement.executeUpdate(sql);
            }
            con.commit();
            System.out.println("事务提交成功");
        } catch (SQLException e) {
            System.out.println("事务提交失败");
            try {
                // 失败则回滚事务
                con.rollback();
            } catch (SQLException ex) {
                System.out.println("事务回滚失败");
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                // 最终一定要恢复自动提交
                con.setAutoCommit(true);
            } catch (SQLException e) {
                System.out.println("设置自动提交失败");
                e.printStackTrace();
            }
        }
    }

}
